package com.gemcoder.system.service;
import static com.gemcoder.system.datasource.JdbcTemplateUtil.executeSQL;
import java.util.ArrayList;
import java.util.List;
import java.util.HashMap;
import java.util.Map;
import org.springframework.stereotype.Service;
import static com.gemcoder.system.service.UserSdkService.sdkOfGetCurrentUserName;
import static com.gemcoder.system.service.UserSdkService.sdkOfGetCurrentUserId;
import com.gemcoder.system.bo.Result;
import static com.gemcoder.system.service.permission.PermissionSdkService.sdkOfgetAllPermissions;
import static com.gemcoder.system.service.permission.PermissionSdkService.sdkOfgetAllMenus;
import java.sql.SQLException;
import org.springframework.security.crypto.bcrypt.BCryptPasswordEncoder;
import cn.hutool.core.util.StrUtil;

@Service
public class ProjectrunreportService {

    public Result fetchProjectData(Map<String,Object> paramMap){
    String projectName = paramMap.get("ProjectNamevalue1") != null ? String.valueOf(paramMap.get("ProjectNamevalue1")) : null;
    String projectNumber = paramMap.get("ProjectNumbervalue1") != null ? String.valueOf(paramMap.get("ProjectNumbervalue1")) : null;
    String monthlyValue = paramMap.get("Monthlyvalue1") != null ? String.valueOf(paramMap.get("Monthlyvalue1")) : null;
    Integer current = paramMap.get("current") != null ? Integer.valueOf(String.valueOf(paramMap.get("current"))) : 1;
    Integer pageSize = paramMap.get("pageSize") != null ? Integer.valueOf(String.valueOf(paramMap.get("pageSize"))) : 10;

    StringBuilder sql = new StringBuilder("SELECT por.id, p.project_name, p.project_code, por.generated_date, por.operation FROM project_operation_report por JOIN project p ON por.project_id = p.id WHERE 1=1");
    List<Object> params = new ArrayList<>();

    if (projectName != null && !projectName.isEmpty()) {
        sql.append(" AND p.project_name like ?");
        params.add("%"+projectName+"%");
    }
    if (projectNumber != null && !projectNumber.isEmpty()) {
        sql.append(" AND p.project_code like ?");
        params.add("%"+projectNumber+"%");
    }
    if (monthlyValue != null && !monthlyValue.isEmpty()) {
        sql.append(" AND DATE_FORMAT(por.generated_date, '%Y-%m') = ?");
        params.add(monthlyValue);
    }

    // Pagination
    int offset = (current - 1) * pageSize;
    sql.append(" LIMIT ?, ?");
    params.add(offset);
    params.add(pageSize);

    List<Map<String, Object>> data = executeSQL(sql.toString(), params.toArray());

    // Get total count
    StringBuilder countSql = new StringBuilder("SELECT COUNT(*) as total FROM project_operation_report por JOIN project p ON por.project_id = p.id WHERE 1=1");
    List<Object> countParams = new ArrayList<>();
    if (projectName != null && !projectName.isEmpty()) {
        countSql.append(" AND p.project_name like ?");
        countParams.add("%"+projectName+"%");
    }
    if (projectNumber != null && !projectNumber.isEmpty()) {
        countSql.append(" AND p.project_code like ?");
        countParams.add("%"+projectNumber+"%");
    }
    if (monthlyValue != null && !monthlyValue.isEmpty()) {
        countSql.append(" AND DATE_FORMAT(por.generated_date, '%Y-%m') = ?");
        countParams.add(monthlyValue);
    }
    List<Map<String, Object>> countResult = executeSQL(countSql.toString(), countParams.toArray());
    int total = countResult.get(0).get("total") != null ? Integer.valueOf(String.valueOf(countResult.get(0).get("total"))) : 0;

    Map<String, Object> result = new HashMap<>();
    result.put("total", total);
    result.put("data", data);

    return new Result(true, "Query successful", result);
}

public Result fetchAllProjectData(Map<String,Object> paramMap){
    Integer current = paramMap.get("current") != null ? Integer.valueOf(String.valueOf(paramMap.get("current"))) : 1;
    Integer pageSize = paramMap.get("pageSize") != null ? Integer.valueOf(String.valueOf(paramMap.get("pageSize"))) : 10;

    StringBuilder sql = new StringBuilder("SELECT por.id, p.project_name, p.project_code, por.generated_date, por.operation FROM project_operation_report por JOIN project p ON por.project_id = p.id LIMIT ?, ?");
    List<Object> params = new ArrayList<>();
    int offset = (current - 1) * pageSize;
    params.add(offset);
    params.add(pageSize);

    List<Map<String, Object>> data = executeSQL(sql.toString(), params.toArray());

    // Get total count
    String countSql = "SELECT COUNT(*) as total FROM project_operation_report por JOIN project p ON por.project_id = p.id";
    List<Map<String, Object>> countResult = executeSQL(countSql);
    int total = countResult.get(0).get("total") != null ? Integer.valueOf(String.valueOf(countResult.get(0).get("total"))) : 0;

    Map<String, Object> result = new HashMap<>();
    result.put("total", total);
    result.put("data", data);

    return new Result(true, "Query successful", result);
}

public Result exportCurrentRowData(Map<String,Object> paramMap){
    Integer id = paramMap.get("id") != null ? Integer.valueOf(String.valueOf(paramMap.get("id"))) : null;

    if (id == null) {
        return new Result(false, "ID is required", null);
    }

    String sql = "SELECT por.id, p.project_name, p.project_code, por.generated_date, por.operation FROM project_operation_report por JOIN project p ON por.project_id = p.id WHERE por.id = ?";
    List<Map<String, Object>> data = executeSQL(sql, id);

    if (data.isEmpty()) {
        return new Result(false, "No data found for the given ID", null);
    }

    // Assuming we have a utility to export data to a file
    List<Map<String, Object>> dataList = new ArrayList<>();
    dataList.add(data.get(0));
    boolean exportSuccess = exportDataToFile(dataList);

    if (exportSuccess) {
        return new Result(true, "Export successful", null);
    } else {
        return new Result(false, "Export failed", null);
    }
}

public Result exportCurrentMonthData(Map<String,Object> paramMap){
    String projectName = paramMap.get("projectName") != null ? String.valueOf(paramMap.get("projectName")) : null;
    String monthlyValue = paramMap.get("Monthlyvalue1") != null ? String.valueOf(paramMap.get("Monthlyvalue1")) : null;

    if (projectName == null || monthlyValue == null) {
        return new Result(false, "Project name and monthly value are required", null);
    }

    String sql = "SELECT por.id, p.project_name, p.project_code, por.generated_date, por.operation FROM project_operation_report por JOIN project p ON por.project_id = p.id WHERE p.project_name like ? AND DATE_FORMAT(por.generated_date, '%Y-%m') = ?";
    List<Map<String, Object>> data = executeSQL(sql, "%"+projectName+"%", monthlyValue);

    if (data.isEmpty()) {
        return new Result(false, "No data found for the given project and month", null);
    }

    // Assuming we have a utility to export data to a file
    boolean exportSuccess = exportDataToFile(data);

    if (exportSuccess) {
        return new Result(true, "Export successful", null);
    } else {
        return new Result(false, "Export failed", null);
    }
}private boolean exportDataToFile(List<Map<String, Object>> data) {
    // Implement the logic to export a list of data to a file
    // This is a placeholder implementation
    return true;
}

}